from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError

class OracleDB:
    def __init__(self, user, password, host, port, service_name):
        """
        初始化 Oracle 数据库连接
        :param user: 用户名
        :param password: 密码
        :param host: 主机地址
        :param port: 端口号
        :param service_name: 服务名
        """
        self.connection_string = f"oracle+oracledb://{user}:{password}@{host}:{port}/{service_name}"
        self.engine = None
        self.Session = None

    def connect(self):
        """
        连接到 Oracle 数据库
        """
        try:
            self.engine = create_engine(self.connection_string)
            self.Session = sessionmaker(bind=self.engine)
            print("Connected to Oracle database successfully!")
        except SQLAlchemyError as e:
            print(f"Failed to connect to Oracle database: {e}")
            raise

    def disconnect(self):
        """
        断开与 Oracle 数据库的连接
        """
        if self.engine:
            self.engine.dispose()
            print("Disconnected from Oracle database.")

    def execute_sql(self, sql, params=None):
        """
        执行 SQL 语句（如 INSERT, UPDATE, DELETE）
        :param sql: SQL 语句
        :param params: SQL 参数（可选）
        :return: 受影响的行数
        """
        try:
            with self.engine.connect() as connection:
                result = connection.execute(text(sql), params or {})
                connection.commit()
                print(f"SQL executed successfully: {sql}")
                return result.rowcount
        except SQLAlchemyError as e:
            print(f"Failed to execute SQL: {e}")
            raise

    def fetch_all(self, sql, params=None):
        """
        查询所有数据
        :param sql: SQL 查询语句
        :param params: SQL 参数（可选）
        :return: 查询结果（列表形式）
        """
        try:
            with self.engine.connect() as connection:
                result = connection.execute(text(sql), params or {})
                rows = result.fetchall()
                print(f"Fetched {len(rows)} rows.")
                return rows
        except SQLAlchemyError as e:
            print(f"Failed to fetch data: {e}")
            raise

    def fetch_one(self, sql, params=None):
        """
        查询单条数据
        :param sql: SQL 查询语句
        :param params: SQL 参数（可选）
        :return: 查询结果（单条记录）
        """
        try:
            with self.engine.connect() as connection:
                result = connection.execute(text(sql), params or {})
                row = result.fetchone()
                print("Fetched one row.")
                return row
        except SQLAlchemyError as e:
            print(f"Failed to fetch data: {e}")
            raise

    def call_procedure(self, procedure_name, params=None):
        """
        调用存储过程
        :param procedure_name: 存储过程名称
        :param params: 存储过程参数（可选）
        """
        try:
            with self.engine.connect() as connection:
                # 使用 SQLAlchemy 的 text() 函数调用存储过程
                sql = f"CALL {procedure_name}({', '.join([':' + p for p in params.keys()])})"
                connection.execute(text(sql), params or {})
                connection.commit()
                print(f"Procedure {procedure_name} executed successfully.")
        except SQLAlchemyError as e:
            print(f"Failed to call procedure: {e}")
            raise
